﻿using System.Collections.Generic;
using System.Data;
using System.Text;
using Utils;
using DataHelper;
namespace DAL
{
    public class EmpChangeDao
    {
        #region 职位变动

        public bool Insert( SortedDictionary<string, string> sdList, SortedDictionary<string, string> hList)
        {        
            StringBuilder builder = new StringBuilder();
            //TestTimeLong=" + sdList["TestTimeLong"] + ",
            builder.Append("Update Admin_Employee Set StateID=" + hList["StateID"] + ",DutyID=" + hList["DutyID"] + ",DeptID=" + hList["DeptID"] + ",AreaID=" + hList["AreaID"] + "  Where EmpCode='" + hList["EmpCode"] + "';");
            builder.Append("Insert into Admin_Emp_Change (EmpCode,EmpName,OldDeptName,OldDutyName,OldAreaName,StateName,NewStateName,NewDeptName,NewDutyName,NewAreaName,ChangeTime,ChangeTypeName,TestTimeLong,Account,Memo) values ");
            builder.Append("('" + sdList["EmpCode"] + "','" + sdList["EmpName"] + "','" + sdList["OldDeptName"] + "','" + sdList["OldDutyName"] + "','" + sdList["OldAreaName"] + "','" + sdList["StateName"] + "','" + sdList["NewStateName"] + "'");
            builder.Append(",'" + sdList["NewDeptName"] + "','" + sdList["NewDutyName"] + "','" + sdList["NewAreaName"] + "','" + sdList["ChangeTime"] + "','" + sdList["ChangeTypeName"] + "'," + sdList["TestTimeLong"] + ",'" + sdList["Account"] + "','" + sdList["Memo"] + "');");
            return SQLHelper.ExecuteSqlWithTransaction(builder.ToString());
          //  return new BuiltSQL().InsertTable(tableName, sdList);
        }


        public bool Update(SortedDictionary<string, string> sdList, SortedDictionary<string, string> hList)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("Update Admin_Employee Set StateID=" + hList["StateID"] + ",DutyID=" + hList["DutyID"] + ",DeptID=" + hList["DeptID"] + ",AreaID=" + hList["AreaID"] + "  Where EmpCode='" + hList["EmpCode"] + "';");
            builder.Append("Update Admin_Emp_Change set ");
            builder.Append("EmpCode='" + sdList["EmpCode"] + "',EmpName='" + sdList["EmpName"] + "',OldDeptName='" + sdList["OldDeptName"] + "',OldDutyName='" + sdList["OldDutyName"] + "',OldAreaName='" + sdList["OldAreaName"] + "',StateName='" + sdList["StateName"] + "',NewStateName='" + sdList["NewStateName"] + "'");
            builder.Append(",NewDeptName='" + sdList["NewDeptName"] + "',NewDutyName='" + sdList["NewDutyName"] + "',NewAreaName='" + sdList["NewAreaName"] + "',ChangeTime='" + sdList["ChangeTime"] + "',ChangeTypeName='" + sdList["ChangeTypeName"] + "',TestTimeLong=" + sdList["TestTimeLong"] + ",Account='" + sdList["Account"] + "',Memo='" + sdList["Memo"] + "' where ChangeID=" + sdList["ChangeID"] + ";");

            return SQLHelper.ExecuteSqlWithTransaction(builder.ToString());
        }


        public bool DeleteOneInfo(string changeID)
        {

            return SQLHelper.ExecuteSqlWithTransaction("Delete from Admin_Emp_Change Where ChangeID=" + changeID);
        }
        //获取一条记录
        public DataTable GetOneInfo(string changeID)
        {
            return SQLHelper.ExecuteSqlGetDrToDt("Select * from Admin_Emp_Change Where ChangeID=" + changeID);
        }
        //职位变动列表SQL
        public string GetSQL(string serWord, string bTime, string eTime, string changeTypeName, string deptID)
        {
            StringBuilder strSQL = new StringBuilder("Select * From Admin_Emp_Change Where OldDutyName<>'售货员' ");
            if (!string.IsNullOrWhiteSpace(serWord) && serWord != "null")
                strSQL.Append(" And (EmpCode='" + serWord + "' Or EmpName Like '%" + serWord + "%')");
            else
            {
                if (!string.IsNullOrWhiteSpace(deptID) && deptID != "0")
                    strSQL.Append("and OldDeptName=(select top 1 DeptName From View_Emp WHERE deptID=" + deptID + " )");
                if (changeTypeName != "")
                    strSQL.Append(" and ChangeTypeName ='" + changeTypeName + "' ");

                if (RegexRule.IsDate(bTime))
                    strSQL.Append(" And ChangeTime>='" + bTime + "'");
                if (RegexRule.IsDate(eTime))
                    strSQL.Append(" And ChangeTime<='" + eTime + "'");
            }
            return strSQL.ToString();
        }
        //职位变动列表
        public DataTable GetLists(string serWord, string bTime, string eTime, string changeTypeName, string deptID)
        {
            DataTable dt = SQLHelper.ExecuteSqlGetDrToDt(GetSQL(serWord, bTime, eTime, changeTypeName, deptID));
            return dt;
        }

        //根据类型，获取所有状态，显示在桌面上
        public DataTable GetStatuByType(string bTime, string eTime)
        {
            StringBuilder strSQL = new StringBuilder("Select ChangeTypeName,count(ChangeTypeName) as ChangeNum From Admin_Emp_Change Where OldDutyName<>'售货员' ");
            if (RegexRule.IsDate(bTime))
                strSQL.Append(" And ChangeTime>='" + bTime + "'");
            if (RegexRule.IsDate(eTime))
                strSQL.Append(" And ChangeTime<='" + eTime + "'");

            strSQL.Append(" group by ChangeTypeName order by count(ChangeTypeName) desc ");
            return SQLHelper.ExecuteSqlGetDrToDt(strSQL.ToString());
        }

        #endregion
    }
}
